package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInOut;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

public class MM_Form_1T extends SvrProcess {
	//Form_1T_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_M_InOut_ID;
	/** */
	String ResultPath;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("M_InOut_ID")) 
				p_M_InOut_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_1T_";
		Integer pathI = 0;
		boolean flag = true;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xls");
			try { 
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(";
					ResultPath = ResultPath.substring(0, 9) + (++pathI).toString() + ")";  
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" +(++pathI).toString() + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell style
		//New Cell Style
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom((short)1);
		cs.setBorderTop((short)1);
		cs.setBorderLeft((short)1);
		cs.setBorderRight((short)1);
		cs.setFont(sheet.getRow(12).getCell(0).getCellStyle().getFont(wb));
		cs.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		
		//Date Cell Styling
		CellStyle ds = wb.createCellStyle();
		ds.setBorderBottom((short)1);
		ds.setFont(sheet.getRow(12).getCell(0).getCellStyle().getFont(wb));
		ds.setDataFormat(wb.createDataFormat().getFormat("dd.MM.yyyy"));
		ds.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		
		//Header
		sheet.getRow(11).getCell(52).setCellValue((new MInOut(m_ctx, p_M_InOut_ID, get_TrxName()).getDocumentNo()));
		sheet.getRow(11).getCell(56).setCellValue((new MInOut(m_ctx, p_M_InOut_ID, get_TrxName()).getDateAcct()));
		
		sheet.getRow(12).getCell(8).setCellValue(MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(12).getCell(56).setCellValue((new MBPartner(m_ctx, (MOrg.get(m_ctx, (new MInOut(m_ctx, p_M_InOut_ID, get_TrxName()).getAD_Org_ID())).getLinkedC_BPartner_ID(get_TrxName())), get_TrxName()).getFI_BIN_IIN()));
		
		sheet.getRow(14).getCell(8).setCellValue((new MBPartner(m_ctx, (new MInOut(m_ctx, p_M_InOut_ID, get_TrxName()).getC_BPartner_ID()), get_TrxName()).getName()));
		sheet.getRow(14).getCell(56).setCellValue((new MBPartner(m_ctx, (new MInOut(m_ctx, p_M_InOut_ID, get_TrxName()).getC_BPartner_ID()), get_TrxName()).getFI_BIN_IIN()));
		
		sheet.getRow(16).getCell(6).setCellValue((new MBPartner(m_ctx, (new MInOut(m_ctx, p_M_InOut_ID, get_TrxName()).getC_BPartner_ID()), get_TrxName()).getName()));
		sheet.getRow(16).getCell(56).setCellValue((new MBPartner(m_ctx, (new MInOut(m_ctx, p_M_InOut_ID, get_TrxName()).getC_BPartner_ID()), get_TrxName()).getFI_BIN_IIN()));							

		//Position product section
		int row = 27;
		int mrow = 0;
		float sumAmount = 0;
		float sumWeight = 0;
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "SELECT 	p.Name AS Product " + 
							" , p.Value AS ProductCode " + 
							" , uom.Name AS UOM " + 
							" , uom.UOMSymbol AS UOMSymbol " + 
							" , iol.MovementQty AS Qty " + 
							" , round((fa.AmtAcctCr / iol.MovementQty)) AS Price " + 
							" , rlt.Name AS PackageType " + 
							" , iol.NoPackages " + 
							" , Round(iol.Weight) AS Weight " + 
							" , fa.AmtAcctCr AS Amount " +
							" , iol.MM_WeightMethod AS WeightMethod " + 
						" FROM fact_acct fa " + 
						" INNER JOIN m_inout io ON io.M_InOut_ID = fa.Record_ID " + 
						" INNER JOIN M_InOutLine iol ON (iol.M_InOut_ID = io.M_InOut_ID AND iol.M_InOutLine_ID = fa.Line_ID) " + 
						" INNER JOIN M_Product p ON p.M_Product_ID = iol.M_Product_ID " + 
						" INNER JOIN C_UOM_Trl uom ON uom.C_UOM_ID = iol.C_UOM_ID " + 
						" LEFT JOIN AD_Ref_List rl ON rl.Value = iol.MM_PackageType " + 
						" INNER JOIN AD_Reference re ON (re.AD_Reference_ID = rl.AD_Reference_ID AND re.Name = 'MM_PackageType') " +
						" LEFT JOIN AD_Ref_List_Trl rlt ON rlt.AD_Ref_List_ID = rl.AD_Ref_List_ID " + 
						" WHERE fa.record_id = " + p_M_InOut_ID +  
							" AND fa.Counter > fa.Reference";
		
		try {
			st = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = st.executeQuery();
			
			while (rs.next()) {
				//Shifting and merging
				sheet.shiftRows(row, row + 106, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 6, 11));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 12, 18));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 19, 23));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 27));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 28, 33));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 34, 39));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 45));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 46, 51));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 52, 57));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 58, 64));
				
				R = sheet.createRow(row);
				for(int j = 0; j < 65; j++){
					C = R.createCell(j);
					C.setCellStyle(cs);
				}
				
				sheet.shiftRows(row + 43, row + 106, 1);
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 0, 15));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 16, 22));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 23, 29));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 30, 36));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 37, 44));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 45, 48));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 49, 54));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 55, 59));
				sheet.addMergedRegion(new CellRangeAddress(row + 43, row + 43, 60, 64));
				
				R = sheet.createRow(row + 43);
				for(int j = 0; j < 65; j++){
					C = R.createCell(j);
					C.setCellStyle(cs);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(rs.getString("Product"));
				sheet.getRow(row).getCell(6).setCellValue(rs.getString("ProductCode"));
				sheet.getRow(row).getCell(12).setCellValue(rs.getString("UOM"));
				sheet.getRow(row).getCell(19).setCellValue(rs.getString("UOMSymbol"));
				sheet.getRow(row).getCell(24).setCellValue(rs.getInt("Qty"));
				sheet.getRow(row).getCell(28).setCellValue(rs.getInt("Price"));
				sheet.getRow(row).getCell(34).setCellValue(rs.getString("PackageType"));
				sheet.getRow(row).getCell(40).setCellValue(rs.getInt("NoPackages"));
				sheet.getRow(row).getCell(46).setCellValue(rs.getFloat("Weight"));
				sumWeight = sumWeight + rs.getFloat("Weight");
				sheet.getRow(row).getCell(52).setCellValue(rs.getFloat("Amount"));
				sumAmount = sumAmount + rs.getFloat("Amount");
				
				sheet.getRow(row + 43).getCell(0).setCellValue(rs.getString("Product"));
				sheet.getRow(row + 43).getCell(23).setCellValue(rs.getString("PackageType"));
				sheet.getRow(row + 43).getCell(30).setCellValue(rs.getInt("NoPackages"));
				sheet.getRow(row + 43).getCell(37).setCellValue(rs.getString("WeightMethod"));
				sheet.getRow(row + 43).getCell(60).setCellValue(rs.getFloat("Weight"));
				mrow++;
				row++;
			}
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, st);
			rs = null;
			st = null;
		}
		
		sheet.getRow(row + 5).getCell(52).setCellValue(sumAmount);
		sheet.getRow(row + 6).getCell(40).setCellValue(sumWeight);
		sheet.getRow(row + 44).getCell(60).setCellValue(sumWeight);
		
//		sheet.getRow(row + 8).getCell(0).setCellValue();//otpusk razreshil doljnost
//		sheet.getRow(row + 8).getCell(12).setCellValue();//otpusk rareshil fio
//		sheet.getRow(row + 8).getCell(30).setCellValue();//glav buh fio
		
		//Position Second section
		
		PreparedStatement stTrans = null;
		ResultSet rsTrans = null;
		String sqlTrans = "SELECT tr.DateDelivery " + 
							" ,tr.DocumentNo " + 
							" ,tr.MM_Shipper_ID " + 
							" ,tr.MM_VehicleRegNo " + 
							" ,tr.MM_VehicleModel " + 
							" ,tr.MM_WaybillNo " + 
							" ,tr.MM_Customer_ID " + 
							" ,tr.MM_DriverName " + 
							" ,tr.MM_DriverLicenseNo " + 
							" ,rlt.Name AS TransType" + 
							" ,tr.MM_LoadingPoint" + 
							" ,tr.MM_UnloadingPoint " + 
							" ,tr.MM_PassPosition " + 
							" ,tr.MM_PassName " + 
							" ,tr.MM_TakeDriverName " + 
							" ,tr.MM_PassDriverName " + 
							" ,tr.MM_TakePosition " + 
							" ,tr.MM_TakeName " + 
						" FROM MM_Transportation tr " + 
						" INNER JOIN AD_Ref_List rl ON rl.Value = tr.MM_TransportationType " + 
						" INNER JOIN AD_Reference re ON (re.AD_Reference_ID = rl.AD_Reference_ID AND re.Name = 'MM_TransportationType') " +
						" INNER JOIN AD_Ref_List_Trl rlt ON rlt.AD_Ref_List_ID = rl.AD_Ref_List_ID " + 
						" WHERE M_InOut_ID = " + p_M_InOut_ID;
		
		row = row + 22;
		
		
		try {
			stTrans = DB.prepareStatement(sqlTrans.toString(), get_TrxName());
			rsTrans = stTrans.executeQuery();
			
			while (rsTrans.next()) {
				sheet.getRow(row).getCell(9).setCellValue((new SimpleDateFormat("dd").format(rsTrans.getDate("DateDelivery"))));
				sheet.getRow(row).getCell(12).setCellValue((new SimpleDateFormat("MMMMM").format(rsTrans.getDate("DateDelivery"))));
				sheet.getRow(row).getCell(17).setCellValue((new SimpleDateFormat("Y").format(rsTrans.getDate("DateDelivery"))));
				
				sheet.getRow(row).getCell(62).setCellValue(rsTrans.getString("DocumentNo"));
				
				sheet.getRow(row + 2).getCell(7).setCellValue((new MBPartner(m_ctx, rsTrans.getInt("MM_Shipper_ID"), get_TrxName()).getName()));
				
				sheet.getRow(row + 2).getCell(42).setCellValue(rsTrans.getString("MM_VehicleRegNo"));
				sheet.getRow(row + 2).getCell(48).setCellValue(rsTrans.getString("MM_VehicleModel"));
				sheet.getRow(row + 2).getCell(62).setCellValue(rsTrans.getString("MM_WaybillNo"));
				
				sheet.getRow(row + 4).getCell(10).setCellValue((new MBPartner(m_ctx, rsTrans.getInt("MM_Customer_ID"), get_TrxName()).getName()));
				sheet.getRow(row + 4).getCell(62).setCellValue((new MBPartner(m_ctx, rsTrans.getInt("MM_Customer_ID"), get_TrxName()).getFI_BIN_IIN()));
				
				sheet.getRow(row + 6).getCell(4).setCellValue(rsTrans.getString("MM_DriverName"));
				sheet.getRow(row + 6).getCell(17).setCellValue(rsTrans.getInt("MM_DriverLicenseNo"));
				sheet.getRow(row + 6).getCell(31).setCellValue(rsTrans.getString("TransType"));
				
				sheet.getRow(row + 8).getCell(7).setCellValue(rsTrans.getString("MM_LoadingPoint"));
				sheet.getRow(row + 8).getCell(31).setCellValue(rsTrans.getString("MM_UnloadingPoint"));
				
				sheet.getRow(row + 30 + mrow).getCell(3).setCellValue(rsTrans.getString("MM_PassPosition"));
				sheet.getRow(row + 30 + mrow).getCell(13).setCellValue(rsTrans.getString("MM_PassName"));
				sheet.getRow(row + 30 + mrow).getCell(46).setCellValue(rsTrans.getString("MM_PassDriverName"));
				sheet.getRow(row + 32 + mrow).getCell(12).setCellValue(rsTrans.getString("MM_TakeDriverName"));
				sheet.getRow(row + 32 + mrow).getCell(38).setCellValue(rsTrans.getString("MM_TakePosition"));
				sheet.getRow(row + 32 + mrow).getCell(48).setCellValue(rsTrans.getString("MM_TakeName"));
				
			}
			
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sqlTrans.toString(), e);
		}
		finally {
			DB.close(rsTrans, stTrans);
			rsTrans = null;
			stTrans = null;
		}
		
		row = row + 40 + mrow;
		PreparedStatement stLoad = null;
		ResultSet rsLoad = null;
		String sqlLoad = "SELECT rlt.Name AS Operation " +  
								" , MM_Executor_ID " + 
								" , rlt2.Name AS Method " + 
								" , MM_Method AS MethodCode " + 
								" , MM_TimeOf_Arrival " + 
								" , MM_TimeOf_Departure " + 
							" FROM MM_LoadingOperations lo " + 
							" INNER JOIN MM_Transportation tr ON tr.MM_Transportation_ID = lo.MM_Transportation_ID " + 
							" LEFT JOIN AD_Ref_List rl ON rl.Value = lo.MM_Operation " + 
							" INNER JOIN AD_Reference re ON (re.AD_Reference_ID = rl.AD_Reference_ID AND re.Name = 'MM_Operation') " + 
							" LEFT JOIN AD_Ref_List_Trl rlt ON rlt.AD_Ref_List_ID = rl.AD_Ref_List_ID " + 
							" LEFT JOIN AD_Ref_List rl2 ON rl2.Value = lo.MM_Method " + 
							" INNER JOIN AD_Reference re2 ON (re2.AD_Reference_ID = rl2.AD_Reference_ID AND re2.Name = 'MM_Method') " + 
							" LEFT JOIN AD_Ref_List_Trl rlt2 ON rlt2.AD_Ref_List_ID = rl2.AD_Ref_List_ID " + 
							" WHERE tr.M_InOut_ID = " + p_M_InOut_ID + 
							" ORDER BY MM_TimeOf_Arrival Desc ";
		
		try {
			stLoad = DB.prepareStatement(sqlLoad.toString(), get_TrxName());
			rsLoad = stLoad.executeQuery();
			
			while (rsLoad.next()) {
				//Shifting and merging
				sheet.shiftRows(row, row + 45, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 7));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 8, 16));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 17, 23));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 30));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 31, 38));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 39, 42));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 43, 48));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 49, 54));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 55, 60));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 61, 64));
				
				R = sheet.createRow(row);
				for(int j = 0; j < 65; j++){
					C = R.createCell(j);
					C.setCellStyle(cs);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(rsLoad.getString("Operation"));
				sheet.getRow(row).getCell(8).setCellValue((new MBPartner(m_ctx, rsLoad.getInt("MM_Executor_ID"), get_TrxName()).getName()));
				sheet.getRow(row).getCell(31).setCellValue(rsLoad.getString("Method"));
				sheet.getRow(row).getCell(39).setCellValue(rsLoad.getString("MethodCode"));
				sheet.getRow(row).getCell(43).setCellValue(rsLoad.getString("MM_TimeOf_Arrival"));
				sheet.getRow(row).getCell(43).setCellStyle(ds);
				sheet.getRow(row).getCell(49).setCellValue(rsLoad.getString("MM_TimeOf_Departure"));
				sheet.getRow(row).getCell(49).setCellStyle(ds);
			}
		}
		catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);			
		}
		finally {
			DB.close(rsLoad, stLoad);
			rsLoad = null;
			stLoad = null;
		}
		
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();

		// Open the file with the application associated
		Util.launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}

}
